package com.jsmtr.rms.service.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.*;
import com.jsmtr.rms.service.ILibTalentService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.EnumTools;
import com.jsmtr.rms.utils.StringTools;
import com.jsmtr.rms.utils.db.DbUtil;

@Service
public class LibTalentService implements ILibTalentService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @Override
    public LibTalentType geTalentType(String name) throws Exception {
        return (LibTalentType) dao.findUnique("from LibTalentType where name='" + name + "'");
    }

    @Override
    public LibTalentType geTalentType(String id, int s) throws Exception {
        return (LibTalentType) dao.findUnique("from LibTalentType where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LibTalentType> geTalentTypes() throws Exception {
        return dao.find("from LibTalentType");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserResume> getLibResume(String libid) throws Exception {
        return dao.find("from UserResume where libid='" + libid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserResume> getResumes(String[] ids) throws Exception {
        return dao.find("from UserResume where id in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfo> getProjects(int type) throws Exception {
        return dao.find("from ProjectInfo where recruittype='" + type + "'");
    }

    @Override
    public ProjectInfoFlow getFLow(String fid) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where id='" + fid + "'");
    }

    @Override
    public DUserResume getResume(String id) throws Exception {
        return (DUserResume) dao.findUnique("from DUserResume where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ProjectInfoFlow> getPlanInfoFlows(String pid) throws Exception {
        return dao.find("from PlanInfoFlow where pid='" + pid + "' order by forder");
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> getResumes(int page, int perpage, Map<String, Object> param, UserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from UserResume dur where 1=1");
        StringBuffer countHQL = new StringBuffer("select count(*) from UserResume dur where 1=1");
        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString(), _map)).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<UserResume> list = dao.findLimit(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> getResumes(int page, int perpage, Map<String, Object> param, DUserResume resume, int type) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from DUserResume dur where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from DUserResume dur where 1=1 ");
        StringBuffer condition = new StringBuffer("and dur.proid in(select id from ProjectInfo p where p.recruittype='" + type + "')");

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString(), _map)).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<DUserResume> list = dao.findLimit(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> getGXResumes(int page, int perpage, HRGXUser user) throws Exception {
        StringBuffer queryHQL = new StringBuffer("from HRGXUser dur where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from HRGXUser dur where 1=1 ");
        StringBuffer condition = new StringBuffer(BeanTools.hqlWhere(user, "dur"));

        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public Map<String, Object> getZZResumes(int page, int perpage, HRZZUser user) throws Exception {
        //StringBuffer queryHQL = new StringBuffer("from HRZZUser dur,DUserResume u  where 1=1 ");
        // StringBuffer countHQL = new StringBuffer("select count(*) from HRZZUser dur where 1=1 ");
        StringBuffer queryHQL = new StringBuffer("select dur.*,u.id drid from HR_ZZUSER dur ,  DUserResume u   where 1=1 and dur.code=u.cardno(+) and dur.pid=u.proid(+) ");
        StringBuffer countHQL = new StringBuffer("select count(1) from HR_ZZUSER dur ,  DUserResume u   where 1=1 and dur.code=u.cardno(+) and dur.pid=u.proid(+) ");
        StringBuffer condition = new StringBuffer(BeanTools.hqlWhere(user, "dur"));


        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageSqlQueryMap(page, perpage, queryHQL.toString(), null, countHQL.toString(), dao);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> getSuccessResumes(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("from DUserResume dur where 1=1 ");
        StringBuffer countHQL = new StringBuffer("select count(*) from DUserResume dur where 1=1 ");
        StringBuffer condition = new StringBuffer("and dur.id in(select u.rid from DoEmploymentUser u where u.state='" + EnumTools.ResultState.通过.ordinal() + "')");

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString(), _map)).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<DUserResume> list = dao.findLimit(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    @Override
    public UserResume getUserResume(String id) throws Exception {
        return (UserResume) dao.findUnique("from UserResume where userid='" + id + "'");
    }

    //教育背景
    @SuppressWarnings("unchecked")
    @Override
    public List<UserEducation> getUserEducation(String rid) throws Exception {
        return dao.find("from UserEducation where rid='" + rid + "'");
    }

    //工作经验
    @SuppressWarnings("unchecked")
    @Override
    public List<UserWorkExperience> getUserWorkExperience(String rid) throws Exception {
        return dao.find("from UserWorkExperience where rid='" + rid + "'");
    }

    //培训经历
    @SuppressWarnings("unchecked")
    @Override
    public List<UserTraining> getUserTraining(String rid) throws Exception {
        return dao.find("from UserTraining where rid='" + rid + "'");
    }

    //获奖信息
    @SuppressWarnings("unchecked")
    @Override
    public List<UserRewardPunishment> getUserRewardPunishment(String rid) throws Exception {
        return dao.find("from UserRewardPunishment where rid='" + rid + "'");
    }

    //家庭成员
    @SuppressWarnings("unchecked")
    @Override
    public List<UserFamily> getUserFamily(String rid) throws Exception {
        return dao.find("from UserFamily where rid='" + rid + "'");
    }

    @Override
    public Map<String, Object> getResumesMap(int page, int perpage, Map<String, Object> param, UserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        //StringBuffer queryHQL1 = new StringBuffer("from UserResume dur where 1=1");
        // StringBuffer countHQL1 = new StringBuffer("select count(*) from UserResume dur where 1=1");
        StringBuffer condition = new StringBuffer();
        StringBuffer queryHQL = new StringBuffer();
        queryHQL.append(
                "select dur.id,\n" +
                        "       dur.attendancetime,\n" +
                        "       dur.birthday,\n" +
                        "       dur.cardaddress,\n" +
                        "       dur.cardno,\n" +
                        "       dur.code,\n" +
                        "       dur.distribution,\n" +
                        "       mxusereducation.educationcode education,\n" +
                        "       mxusereducation.educationname educationname,\n" +
                        "       dur.email,\n" +
                        "       dur.expectedsalaryamount,\n" +
                        "       mxusereducation.endtime graduationtime,\n" +
                        "       dur.head,\n" +
                        "       dur.height,\n" +
                        "       dur.hkcode,\n" +
                        "       dur.hkname,\n" +
                        "       dur.hometel,\n" +
                        "       dur.hukouaddress,\n" +
                        "       dur.jg,\n" +
                        "       dur.libid,\n" +
                        "       dur.libname,\n" +
                        "       mxusereducation.majorname majorname,\n" +
                        "       dur.mcode,\n" +
                        "       dur.mname,\n" +
                        "       dur.mzcode,\n" +
                        "       dur.mzname,\n" +
                        "       dur.name,\n" +
                        "       dur.nowaddress,\n" +
                        "       dur.othcode,\n" +
                        "       dur.othdate,\n" +
                        "       dur.othlevel,\n" +
                        "       dur.othname,\n" +
                        "       dur.othorg,\n" +
                        "       dur.othtypecode,\n" +
                        "       dur.othtypename,\n" +
                        "       dur.othzy,\n" +
                        "       dur.postcompany,\n" +
                        "       dur.postgettime,\n" +
                        "       dur.postlevelcode,\n" +
                        "       dur.postlevelname,\n" +
                        "       dur.postspecialty,\n" +
                        "       dur.posttypecode,\n" +
                        "       dur.posttypename,\n" +
                        "       dur.presentsalaryamount,\n" +
                        "       dur.qlgettime,\n" +
                        "       dur.qllevelcode,\n" +
                        "       dur.qllevelname,\n" +
                        "       dur.qllevelpid,\n" +
                        "       dur.qlorg,\n" +
                        "       dur.qltype,\n" +
                        "       dur.sexcode,\n" +
                        "       dur.sexname,\n" +
                        "       dur.tel,\n" +
                        "       mxusereducation.schoolname university,\n" +
                        "       dur.userid,\n" +
                        "       dur.workexperience,\n" +
                        "       dur.zzmmcode,\n" +
                        "       dur.zzmmname,\n" +
                        "       dur.qllevelpname,\n" +
                        "       dur.qllevelscode,\n" +
                        "       dur.qllevelsname,\n" +
                        "       cardnototal.cardcount,\n" +
                        "       dur.resume_type\n" +
                        "  from userresume dur,\n" +
                        "       (select count(1) cardcount, r.cardno from userresume r group by r.cardno) cardnototal,\n" +
                        "( select de2.* from view_zjreducation de2 ) mxusereducation \n" +
                        " where 1 = 1\n" +
                        "   and dur.id=mxusereducation.rid(+) and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer countHQL = new StringBuffer();
        countHQL.append("select count(1) \n" +
                "  from userresume dur,\n" +
                "       (select count(1) cardcount, r.cardno from userresume r group by r.cardno) cardnototal,\n" +
                " ( select de2.* from view_zjreducation de2) mxusereducation\n" +
                "  where 1 = 1 \n" +
                "   and dur.id=mxusereducation.rid(+) and dur.cardno = cardnototal.cardno(+) ");
        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

//        condition.append(BeanTools.hqlWhere(resume, "dur"));
        Optional.ofNullable(resume.getResumeType()).ifPresent(resumeType -> {
            _map.put("resume_type",resumeType);
            condition.append("and dur.resume_type=:resume_type");
        });

        queryHQL.append(condition);
        countHQL.append(condition);
        BigDecimal count = (BigDecimal) dao.executeSQLQuery(countHQL.toString(), _map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);

        StringBuffer orderHql = new StringBuffer();
        orderHql.append(" order by dur.CODE desc ");
        queryHQL.append(orderHql);
        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count.doubleValue());
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> getDResumesMap(int page, int perpage, Map<String, Object> param, DUserResume resume, String type) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        //StringBuffer queryHQL1 = new StringBuffer(" from UserResume dur where 1=1");
        //StringBuffer countHQL1 = new StringBuffer(" select count(*) from UserResume dur where 1=1 ");
        StringBuffer condition = new StringBuffer();


        StringBuffer queryHQL = new StringBuffer();
        queryHQL.append(
                "select dur.id,\n" +
                        "       dur.adddate,\n" +
                        "       dur.attendancetime,\n" +
                        "       dur.bdaddress,\n" +
                        "       dur.bddate,\n" +
                        "       dur.bdlxdh,\n" +
                        "       dur.bdlxr,\n" +
                        "       dur.bdmsg,\n" +
                        "       dur.bdremark,\n" +
                        "       dur.bdstate,\n" +
                        "       dur.bdtime,\n" +
                        "       dur.bduid,\n" +
                        "       dur.bduname,\n" +
                        "       dur.birthday,\n" +
                        "       dur.cardaddress,\n" +
                        "       dur.cardno,\n" +
                        "       dur.code,\n" +
                        "       dur.distribution,\n" +
                        "       dur.editdate,\n" +
                        "       dur.editusername,\n" +
                        "       dur.edituserid,\n" +
                        "        mxdusereducation.educationcode as education,\n" +
                        "        mxdusereducation.educationname as educationname,\n" +
                        "       dur.email,\n" +
                        "       dur.expectedsalaryamount,\n" +
                        "       dur.fid,\n" +
                        "       dur.fqdate,\n" +
                        "       dur.fqfid,\n" +
                        "       dur.fqstate,\n" +
                        "       mxdusereducation.endtime as graduationtime,\n" +
                        "       dur.head,\n" +
                        "       dur.height,\n" +
                        "       dur.hkcode,\n" +
                        "       dur.hkname,\n" +
                        "       dur.hometel,\n" +
                        "       dur.hukouaddress,\n" +
                        "       dur.jg,\n" +
                        "       dur.jid,\n" +
                        "       dur.jname,\n" +
                        "       mxdusereducation.majorname as majorname,\n" +
                        "       dur.mcode,\n" +
                        "       dur.mname,\n" +
                        "       dur.mzcode,\n" +
                        "       dur.mzname,\n" +
                        "       dur.name,\n" +
                        "       dur.nowaddress,\n" +
                        "       dur.othcode,\n" +
                        "       dur.othdate,\n" +
                        "       dur.othlevel,\n" +
                        "       dur.othname,\n" +
                        "       dur.othorg,\n" +
                        "       dur.othtypecode,\n" +
                        "       dur.othtypename,\n" +
                        "       dur.othzy,\n" +
                        "       dur.path,\n" +
                        "       dur.pid,\n" +
                        "       dur.pname,\n" +
                        "       dur.postcompany,\n" +
                        "       dur.postgettime,\n" +
                        "       dur.postlevelcode,\n" +
                        "       dur.postlevelname,\n" +
                        "       dur.postspecialty,\n" +
                        "       dur.posttypecode,\n" +
                        "       dur.posttypename,\n" +
                        "       dur.presentsalaryamount,\n" +
                        "       dur.proid,\n" +
                        "       dur.proname,\n" +
                        "       dur.qlgettime,\n" +
                        "       dur.qllevelcode,\n" +
                        "       dur.qllevelname,\n" +
                        "       dur.qllevelpid,\n" +
                        "       dur.qlorg,\n" +
                        "       dur.qltype,\n" +
                        "       dur.sexcode,\n" +
                        "       dur.sexname,\n" +
                        "       dur.state,\n" +
                        "       dur.sxbz,\n" +
                        "       dur.tel,\n" +
                        "       dur.tjaddress,\n" +
                        "       dur.tjdate,\n" +
                        "       dur.tjlxdh,\n" +
                        "       dur.tjlxr,\n" +
                        "       dur.tjmsg,\n" +
                        "       dur.tjremark,\n" +
                        "       dur.tjstate,\n" +
                        "       dur.tjtime,\n" +
                        "       dur.tjuid,\n" +
                        "       dur.tjuname,\n" +
                        "       mxdusereducation.schoolname as university,\n" +
                        "       dur.userid,\n" +
                        "       dur.workexperience,\n" +
                        "       dur.zldate,\n" +
                        "       dur.zldelay,\n" +
                        "       dur.zljstime,\n" +
                        "       dur.zlkstime,\n" +
                        "       dur.zlmsg,\n" +
                        "       dur.zlmsgcont,\n" +
                        "       dur.zlremark,\n" +
                        "       dur.zlstate,\n" +
                        "       dur.zluid,\n" +
                        "       dur.zluname,\n" +
                        "       dur.zsdate,\n" +
                        "       dur.zsremark,\n" +
                        "       dur.zsstate,\n" +
                        "       dur.zsuid,\n" +
                        "       dur.zsuname,\n" +
                        "       dur.zzmmcode,\n" +
                        "       dur.zzmmname,\n" +
                        "       dur.op1,\n" +
                        "       dur.op2,\n" +
                        "       dur.op3,\n" +
                        "       dur.op4,\n" +
                        "       dur.op5,\n" +
                        "       dur.rcode,\n" +
                        "       dur.region,\n" +
                        "       dur.gwdate,\n" +
                        "       dur.gwuid,\n" +
                        "       dur.gwuname,\n" +
                        "       dur.gwstate,\n" +
                        "       dur.op6,\n" +
                        "       dur.rfid,\n" +
                        "       dur.orid,\n" +
                        "       dur.nrid,\n" +
                        "       dur.njname,\n" +
                        "       dur.changedate,\n" +
                        "       dur.qllevelpname,\n" +
                        "       dur.qllevelscode,\n" +
                        "       dur.qllevelsname,\n" +
                        "       dur.rcount,\n" +
                        "       dur.op7,\n" +
                        "       dur.sxstate,\n" +
                        "       dur.sxremark,\n" +
                        "       dur.sxuid,\n" +
                        "       dur.sxuname,\n" +
                        "       dur.sxdate,\n" +
                        "       dur.op9,\n" +
                        "       dur.qystate,\n" +
                        "       dur.qyremark,\n" +
                        "       dur.qyuid,\n" +
                        "       dur.qyuname,\n" +
                        "       dur.qydate,\n" +
                        "       cardnototal.cardcount\n" +
                        "  from duserresume dur,\n" +
                        "       (select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal,\n" +
                        "   ( select de2.* from view_zjeducation de2) mxdusereducation \n" +
                        "   where 1 = 1\n" +
                        "   and dur.id=mxdusereducation.rid(+) and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer countHQL = new StringBuffer();
        countHQL.append("select count(1) \n" +
                "  from duserresume dur,\n" +
                "       (select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal,\n" +
                "       ( select de2.* from view_zjeducation de2) mxdusereducation \n" +
                " where 1 = 1\n" +
                "   and dur.id=mxdusereducation.rid(+) and dur.cardno = cardnototal.cardno(+) \n");
        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom ");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto ");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom ");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto ");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom ");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto ");
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));
        if (StringTools.trimNotEmpty(type)) {
            condition.append(" and dur.proid like '%'||:ptype||'_%' ");
            _map.put("ptype", type);
        }
        queryHQL.append(condition);
        countHQL.append(condition);

        StringBuffer orderHql = new StringBuffer();
        orderHql.append(" order by dur.adddate desc ");
        queryHQL.append(orderHql);
        BigDecimal count = (BigDecimal) dao.executeSQLQuery(countHQL.toString(), _map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);

        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("count", count.doubleValue());
        map.put("list", list);
        return map;
    }
}
